package com.oocl.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.oocl.dao.MerchantDao;
import com.oocl.pojo.Merchant;
import com.oocl.util.DBUtil;

public class MerchantDaoImpl implements MerchantDao {

	@Override
	public int add(Merchant t) {
		String sql="insert into MERCHANT (ID,NAME,IDCARD,HEADING,ADDRESS,STATUS,BAN,BCONTENT) values (?,?,?,?,?,?,?,?)";
		Connection connection = null;
		PreparedStatement pst=null;
		int m=0;
		try {
			connection = DBUtil.getConnection();
			pst = connection.prepareStatement(sql);
			pst.setString(1, t.getId());
			pst.setString(2, t.getName());
			pst.setString(3, t.getIdcard());
			pst.setString(4, t.getHeading());
			pst.setString(5, t.getAddress());
			pst.setInt(6, t.getStatus());
			pst.setBoolean(7, t.isBan());
			pst.setString(8, t.getbContent());
			m=pst.executeUpdate();
		} catch (SQLException e) {
			throw new RuntimeException("MerchantDaoImpl insert error");
		} finally{
			DBUtil.release(null, pst, null);
		}
		return m;
	}

	@Override
	public int update(Merchant t) {
		String sql="update MERCHANT set NAME=?,IDCARD=?,HEADING=?,ADDRESS=?,STATUS=?,BAN=?,BCONTENT=? where ID=?";
		Connection connection = null;
		PreparedStatement pst=null;
		int m=0;
		try {
			connection = DBUtil.getConnection();
			pst = connection.prepareStatement(sql);
			pst.setString(1, t.getName());
			pst.setString(2, t.getIdcard());
			pst.setString(3, t.getHeading());
			pst.setString(4, t.getAddress());
			pst.setInt(5, t.getStatus());
			pst.setBoolean(6, t.isBan());
			pst.setString(7, t.getbContent());
			pst.setString(8, t.getId());
			m=pst.executeUpdate();
		} catch (SQLException e) {
			throw new RuntimeException("MerchantDaoImpl update error");
		} finally{
			DBUtil.release(null, pst, null);
		}
		return m;
	}

	@Override
	public int delete(String id) {
		String sql="delete from MERCHANT where ID=?";
		Connection connection = null;
		PreparedStatement pst=null;
		int m=0;
		try {
			connection = DBUtil.getConnection();
			pst = connection.prepareStatement(sql);
			pst.setString(1, id);
			m=pst.executeUpdate();
		} catch (SQLException e) {
			throw new RuntimeException("MerchantDaoImpl delete error");
		} finally{
			DBUtil.release(null, pst, null);
		}
		return m;
	}

	@Override
	public List<Merchant> findAll() {
		List<Merchant> list = new ArrayList<Merchant>();
		Connection connection = null;
		PreparedStatement pst = null;
		String sql = "select * from MERCHANT";
		ResultSet rs = null;
		try {
			connection = DBUtil.getConnection();
			pst=connection.prepareStatement(sql);
			rs = pst.executeQuery();
			while(rs.next()){
				Merchant merchant = new Merchant();
				merchant.setId(rs.getString("id"));
				merchant.setName(rs.getString("name"));
				merchant.setIdcard(rs.getString("idcard"));
				merchant.setHeading(rs.getString("heading"));
				merchant.setAddress(rs.getString("address"));
				merchant.setStatus(rs.getInt("status"));
				merchant.setBan(rs.getBoolean("ban"));
				merchant.setbContent(rs.getString("bcontent"));
				list.add(merchant);
			}
		} catch (SQLException e) {
			throw new RuntimeException("MerchantDaoImpl findAll error");
		} finally{
			DBUtil.release(rs, pst, null);
		}
		return list;
	}

	@Override
	public Merchant findMerchantById(String id) {
		Connection connection = null;
		PreparedStatement pst = null;
		String sql = "select * from MERCHANT where ID=?";
		ResultSet rs = null;
		Merchant merchant = null;
		try {
			connection = DBUtil.getConnection();
			pst=connection.prepareStatement(sql);
			pst.setString(1, id);
			rs = pst.executeQuery();
			merchant = null;
			while(rs.next()){
				merchant = new Merchant();
				merchant.setId(rs.getString("id"));
				merchant.setName(rs.getString("name"));
				merchant.setIdcard(rs.getString("idcard"));
				merchant.setHeading(rs.getString("heading"));
				merchant.setAddress(rs.getString("address"));
				merchant.setStatus(rs.getInt("status"));
				merchant.setBan(rs.getBoolean("ban"));
				merchant.setbContent(rs.getString("bcontent"));
			}
		} catch (SQLException e) {
			throw new RuntimeException("MerchantDaoImpl findMerchantById error");
		} finally{
			DBUtil.release(rs, pst, null);
		}
		return merchant;
	}

	@Override
	public int getStatusById(String id) {
		Connection connection = null;
		PreparedStatement pst = null;
		String sql = "select status from MERCHANT where ID=?";
		int status = -1;
		ResultSet rs = null;
		try {
			connection = DBUtil.getConnection();
			pst=connection.prepareStatement(sql);
			pst.setString(1, id);
			rs = pst.executeQuery();
			while(rs.next()){
				status = rs.getInt("status");
			}
		} catch (SQLException e) {
			throw new RuntimeException("MerchantDaoImpl getStatusById error");
		} finally{
			DBUtil.release(rs, pst, null);
		}
		return status;
	}

	@Override
	public List<Merchant> findMerchantByStatus(Integer status) {
		Connection connection = null;
		PreparedStatement pst = null;
		String sql = "select * from MERCHANT where STATUS=?";
		List<Merchant> list = new ArrayList<Merchant>();
		ResultSet rs=null;
		try {
			connection = DBUtil.getConnection();
			pst=connection.prepareStatement(sql);
			pst.setInt(1, status);
			rs = pst.executeQuery();
			while(rs.next()){
				Merchant merchant = new Merchant();
				merchant.setId(rs.getString("id"));
				merchant.setName(rs.getString("name"));
				merchant.setIdcard(rs.getString("idcard"));
				merchant.setHeading(rs.getString("heading"));
				merchant.setAddress(rs.getString("address"));
				merchant.setStatus(rs.getInt("status"));
				merchant.setBan(rs.getBoolean("ban"));
				merchant.setbContent(rs.getString("bcontent"));
				list.add(merchant);
			}
		} catch (SQLException e) {
			throw new RuntimeException("MerchantDaoImpl findMerchantByStatus error");
		} finally{
			DBUtil.release(rs, pst, null);
		}
		return list;
	}

	@Override
	public List<Merchant> findMerchantByBan(Boolean ban) {
		List<Merchant> list = new ArrayList<Merchant>();
		Connection connection = null;
		PreparedStatement pst = null;
		String sql = "select * from MERCHANT where ban=?";
		ResultSet rs =null;
		try {
			connection = DBUtil.getConnection();
			pst=connection.prepareStatement(sql);
			pst.setBoolean(1, ban);
			rs = pst.executeQuery();
			while(rs.next()){
				Merchant merchant = new Merchant();
				merchant.setId(rs.getString("id"));
				merchant.setName(rs.getString("name"));
				merchant.setIdcard(rs.getString("idcard"));
				merchant.setHeading(rs.getString("heading"));
				merchant.setAddress(rs.getString("address"));
				merchant.setStatus(rs.getInt("status"));
				merchant.setBan(rs.getBoolean("ban"));
				merchant.setbContent(rs.getString("bcontent"));
				list.add(merchant);
			}
		} catch (SQLException e) {
			throw new RuntimeException("MerchantDaoImpl findMerchantByBan error");
		} finally{
			DBUtil.release(rs, pst, null);
		}
		return list;
	}

	@Override
	public int updateStatus(String id, Integer status) {
		Connection connection = null;
		PreparedStatement pst = null;
		String sql = "update MERCHANT set status=? where id=?";
		int m=0;
		try {
			connection = DBUtil.getConnection();
			pst=connection.prepareStatement(sql);
			pst.setInt(1, status);
			pst.setString(2, id);
			m = pst.executeUpdate();
		} catch (SQLException e) {
			throw new RuntimeException("MerchantDaoImpl updateStatus error");
		} finally{
			DBUtil.release(null, pst, null);
		}
		return m;
	}

	@Override
	public int updateBan(String id, Boolean ban) {
		Connection connection = null;
		PreparedStatement pst = null;
		String sql = "update MERCHANT set ban=? where id=?";
		int m=0;
		try {
			connection = DBUtil.getConnection();
			pst=connection.prepareStatement(sql);
			pst.setBoolean(1, ban);
			pst.setString(2, id);
			m = pst.executeUpdate();
		} catch (SQLException e) {
			throw new RuntimeException("MerchantDaoImpl updateBan error");
		} finally{
			DBUtil.release(null, pst, null);
		}
		return m;
	}

}
